package com.pokermind.repository.dao.impl;

import com.pokermind.model.po.PokerUser;
import com.pokermind.repository.dao.PokerUserDao;
import com.pokermind.repository.mapper.PokerUserRowMapper;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * PokerUser DAO实现类
 * 适配最新的简化表结构
 * 
 * @author PokerMind Team
 */
@Repository
public class PokerUserDaoImpl implements PokerUserDao {
    
    private static final Logger logger = LoggerFactory.getLogger(PokerUserDaoImpl.class);
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private final PokerUserRowMapper rowMapper = new PokerUserRowMapper();
    
    @Override
    public PokerUser save(PokerUser user) {
        String sql = """
            INSERT INTO poker_users (
                player_id, player_name, total_hands, total_profit, vpip_rate, aggression_level,
                win_rate, playing_style, recent_form,  
                create_time, update_time
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """;
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getPlayerId());
            ps.setString(2, user.getPlayerName());
            ps.setObject(3, user.getTotalHands());
            ps.setBigDecimal(4, user.getTotalProfit());
            ps.setBigDecimal(5, user.getVpipRate());
            ps.setString(6, user.getAggressionLevel());
            ps.setBigDecimal(7, user.getWinRate());
            ps.setString(8, user.getPlayingStyle());
            ps.setString(9, user.getRecentForm());
            ps.setLong(10, user.getCreateTime() != null ? user.getCreateTime() : System.currentTimeMillis());
            ps.setLong(11, user.getUpdateTime() != null ? user.getUpdateTime() : System.currentTimeMillis());
            return ps;
        }, keyHolder);
        
        user.setId(keyHolder.getKey().longValue());
        return user;
    }
    
    @Override
    public int update(PokerUser user) {
        String sql = """
            UPDATE poker_users SET
                player_id = ?, player_name = ?, total_hands = ?, total_profit = ?,
                vpip_rate = ?, aggression_level = ?, win_rate = ?,
                playing_style = ?, recent_form = ?, update_time = ?
            WHERE id = ?
        """;
        
        return jdbcTemplate.update(sql,
            user.getPlayerId(),
            user.getPlayerName(),
            user.getTotalHands(),
            user.getTotalProfit(),
            user.getVpipRate(),
            user.getAggressionLevel(),
            user.getWinRate(),
            user.getPlayingStyle(),
            user.getRecentForm(),
            System.currentTimeMillis(), // update_time
            user.getId()
        );
    }
    
    @Override
    public Optional<PokerUser> findById(Long id) {
        String sql = "SELECT * FROM poker_users WHERE id = ?";
        
        try {
            PokerUser user = jdbcTemplate.queryForObject(sql, rowMapper, id);
            return Optional.ofNullable(user);
        } catch (Exception e) {
            logger.debug("用户未找到: id={}", id);
            return Optional.empty();
        }
    }
    
    @Override
    public Optional<PokerUser> findByPlayerId(String playerId) {
        String sql = "SELECT * FROM poker_users WHERE player_id = ?";
        
        try {
            // 首先检查用户是否存在
            String countSql = "SELECT COUNT(*) FROM poker_users WHERE player_id = ?";
            Integer count = jdbcTemplate.queryForObject(countSql, Integer.class, playerId);
            
            if (count == null || count == 0) {
                return Optional.empty();
            }
            
            PokerUser user = jdbcTemplate.queryForObject(sql, rowMapper, playerId);
            return Optional.ofNullable(user);
        } catch (Exception e) {
            logger.error("用户查找异常: playerId={}, error={}", playerId, e.getMessage(), e);
            return Optional.empty();
        }
    }
    
    @Override
    public List<PokerUser> findAll() {
        String sql = "SELECT * FROM poker_users ORDER BY id";
        return jdbcTemplate.query(sql, rowMapper);
    }
    
    @Override
    public int deleteById(Long id) {
        String sql = "DELETE FROM poker_users WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM poker_users";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
    
    @Override
    public boolean existsById(Long id) {
        String sql = "SELECT COUNT(*) FROM poker_users WHERE id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }
    
    @Override
    public int batchSave(List<PokerUser> entities) {
        String sql = """
            INSERT INTO poker_users (
                player_id, player_name, total_hands, total_profit, vpip_rate, aggression_level,
                win_rate, playing_style, recent_form,  create_time, update_time
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """;
        
        List<Object[]> batchArgs = new ArrayList<>();
        for (PokerUser user : entities) {
            batchArgs.add(new Object[] {
                user.getPlayerId(),
                user.getPlayerName(),
                user.getTotalHands(),
                user.getTotalProfit(),
                user.getVpipRate(),
                user.getAggressionLevel(),
                user.getWinRate(),
                user.getPlayingStyle(),
                user.getRecentForm(),
                user.getCreateTime() != null ? user.getCreateTime() : System.currentTimeMillis(),
                user.getUpdateTime() != null ? user.getUpdateTime() : System.currentTimeMillis()
            });
        }
        
        int[] batchResult = jdbcTemplate.batchUpdate(sql, batchArgs);
        
        return batchResult.length;
    }
    
    @Override
    public int delete(PokerUser entity) {
        return deleteById(entity.getId());
    }
    
    /**
     * 查找需要更新行为数据的用户
     */
    @Override
    public List<PokerUser> findUsersNeedingBehaviorUpdate(int hours) {
        String sql = """
            SELECT * FROM poker_users 
            WHERE update_time < ?
               AND total_hands > 0
            ORDER BY update_time ASC
        """;
        
        // 计算小时前的时间戳
        long hoursAgoTimestamp = System.currentTimeMillis() - (hours * 60 * 60 * 1000L);
        return jdbcTemplate.query(sql, rowMapper, hoursAgoTimestamp);
    }
    
    @Override
    public boolean existsByPlayerId(String playerId) {
        String sql = "SELECT COUNT(*) FROM poker_users WHERE player_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, playerId);
        return count != null && count > 0;
    }
    
    @Override
    public List<PokerUser> findTopUsersByProfit(int limit) {
        String sql = "SELECT * FROM poker_users WHERE total_profit IS NOT NULL ORDER BY total_profit DESC LIMIT ?";
        return jdbcTemplate.query(sql, rowMapper, limit);
    }
    
    @Override
    public List<PokerUser> findTopUsersByHands(int limit) {
        String sql = "SELECT * FROM poker_users WHERE total_hands IS NOT NULL ORDER BY total_hands DESC LIMIT ?";
        return jdbcTemplate.query(sql, rowMapper, limit);
    }
    
    @Override
    public int updateUserStats(Long userId, int handsIncrement, java.math.BigDecimal profitIncrement) {
        String sql = """
            UPDATE poker_users SET
                total_hands = COALESCE(total_hands, 0) + ?,
                total_profit = COALESCE(total_profit, 0) + ?,
                update_time = ?
            WHERE id = ?
        """;
        
        return jdbcTemplate.update(sql, handsIncrement, profitIncrement, System.currentTimeMillis(), userId);
    }
    
    /**
     * 批量更新行为数据
     */
    @Override
    public int updateBehaviorData(Long userId, java.math.BigDecimal vpipRate, 
                                 String aggressionLevel, java.math.BigDecimal winRate,
                                 String playingStyle, String recentForm) {
        String sql = """
            UPDATE poker_users SET
                vpip_rate = ?, aggression_level = ?, win_rate = ?,
                playing_style = ?, recent_form = ?, 
                update_time = ?
            WHERE id = ?
        """;
        
        return jdbcTemplate.update(sql, vpipRate, aggressionLevel, winRate,
                                  playingStyle, recentForm, System.currentTimeMillis(), userId);
    }
    
    @Override
    public List<PokerUser> findActiveUsersSince(Long since) {
        String sql = "SELECT * FROM poker_users WHERE update_time >= ? ORDER BY update_time DESC";
        return jdbcTemplate.query(sql, rowMapper, since);
    }
}